<?php
namespace app\houtai\controller\user;
use app\houtai\controller\BaseController;
use think\Db;
use think\Request;
use think\db\Expression;

class PayController extends BaseController {
  const PAY_TYPES = [-1 => '支付类型', 0 => 'WeiXin', 1 => 'IOS', 2 => 'AliPay', 3 => 'WxGzh'];
  /**
   *  @brief  : 用户管理-在线支付
   *  @return : page
   *  @details: 
   */
  public function indexAction() {
    $conds = [];
    $order = [];
    $list = [];
    $STATE_PAY = config('STATE_PAY');
    $STATE_ORD = config('STATE_ORD');
    $CHANNELS  = $this->GetAdminChannel();
    $GAME_KIND = GetGameKind();
    //////////////////////////////////////////////
    $Channel = $this->request->param('Channel/d', -1);
    if ($Channel > 0 && array_key_exists($Channel, $CHANNELS)) {
      $conds['c.Channel'][] = ['IN', array_keys($CHANNELS)];
      $conds['c.Channel'][] = ['EQ', $Channel];
    } else {
      $conds['c.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    //////////////////////////////////////////////
    $KindID = $this->request->param('KindID/d', -1);
    if ($KindID > 0 && array_key_exists($KindID, $GAME_KIND)) {
      $conds['a.KindID'] = $KindID;
    }
    //////////////////////////////////////////////
    $time1 = $this->request->param('time1/d', 0, 'trim,YmdHis2Unix');
    $time2 = $this->request->param('time2/d', 0, 'trim,YmdHis2Unix');
    $timea = YmdHis($time1);
    $timeb = YmdHis($time2);
    if ($time1 > 0) {
      if ($time2 > 0) {
        if ($time1 <= $time2) {
          $conds['a.Addtime'] = ['BETWEEN TIME', [$timea, $timeb]];
        } else {
          $conds['a.Addtime'] = ['>= TIME', $timea];
        }
      } else {
        $conds['a.Addtime'] = ['>= TIME', $timea];
      }
    } else {
      if ($time2 > 0) {
        $conds['a.Addtime'] = ['<= TIME', $timeb];
      }
    }
    //////////////////////////////////////////////
    $ptype = $this->request->param('ptype/s', -1); // 支付类型
    if ($ptype >= 0 && array_key_exists($ptype, self::PAY_TYPES)) {
      $conds['a.PayType'] = self::PAY_TYPES[$ptype];
    }
    
    $state1 = $this->request->param('state1/d', -1);
    if ($state1 > 0 && array_key_exists($state1, $STATE_PAY)) {
      $conds['a.Status'] = $state1;
    }

    $state2 = $this->request->param('state2/d', -1);
    if ($state2 > 0 && array_key_exists($state2, $STATE_ORD)) {
      $conds['a.GameStatus'] = $state2;
    }
    //////////////////////////////////////////////
    $search = $this->request->param('search/s', '', 'trim');
    if (!empty($search)) {
      if (is_numeric($search) && $search > 0 && $search < PHP_INT_MAX) {
        // 充值用户UserID|充值用户GameID|所属代理UID|所属代理邀请码
        $conds['a.UserID|c.GameID|c.SpreaderID|f.GameID'] = $search;
      } else {
        // 充值用户昵称|所属代理姓名|内部订单号
        $conds['c.NickName|f.Compellation|a.MerBillNo|a.TradeNo'] = ['LIKE', "%{$search}%"];
      }
    }
    ////////////////////////////////////////////////////////////////////////////
    $orderMap = [
      'A' => 'ID',
      'B' => 'Channel',
      'C' => 'KindID',
      'D' => 'GameID',
      'E' => 'AGameID',
      'F' => 'MerBillNo',
      'G' => 'RMB',
      'H' => 'PayType',
      'I' => 'Status',
      'J' => 'GameStatus',
      'K' => 'wwwType',
      'L' => 'AddTime',
      'M' => 'fc',
    ];
    $this->orderField = array_key_exists($this->orderField, $orderMap) ? $this->orderField : 'A';
    $order[$orderMap[$this->orderField]] = $this->orderDirection;
    ////////////////////////////////////////////////////////////////////////////
    $list = table('CYWebSMSDB.dbo.ChargeOnline a', 'rydb.websms')->field([
      'a.ID', 'a.UserID', 'a.KindID', 'a.MerBillNo', 'a.RMB', 'a.Status', 'a.GameStatus',
      'a.PayType', 'a.wwwType', 'a.wwwIp', 'b.GameName', 'c.GameID', 'c.NickName',
      'a.TradeNo',
      'CONVERT(VARCHAR(19), a.Addtime, 120)' => 'AddTime',
      'CONVERT(VARCHAR(19), a.Updatetime, 120)' => 'UpdateTime',
      'c.Accounts', 'c.Compellation', 'c.PassPortID', 'c.Sex', 'c.LastLogonIP',
      'c.LastLogonDate', 'c.RegisterIP', 'c.RegisterDate', 'c.AccountsType',
      'c.Channel', 'c.RegisterMobile', 'c.GameLogonCount',
      'c.AccountImgUrl' => 'Avatar', 'ISNULL(c.SpreaderID, 0)' => 'AUserID',
      'ISNULL(f.GameID, 0)' => 'AGameID',
      "ISNULL(f.Compellation, '')" => 'ACompellation',
      'ISNULL(f.AccountsType, 0)' => 'AAccountsType',
      "ISNULL(f.AccountImgUrl, '')" => 'AAvatar',
      '(SELECT COUNT(h.MerBillNo) FROM CYWebSMSDB.dbo.AgentChargeRebate h WHERE h.MerBillNo = a.MerBillNo)' => 'fc',
    ])
    ->join('CYBasicDB.dbo.GameKindItem b', 'b.KindID = a.KindID', 'LEFT')
    ->join('CYBasicDB.dbo.AccountsInfo c', 'c.UserID = a.UserID', 'LEFT')
    ->join('CYBasicDB.dbo.AccountsGameCurrency e', 'e.UserID = a.UserID', 'LEFT')
    ->join('CYBasicDB.dbo.AccountsInfo f', 'f.UserID = c.SpreaderID', 'LEFT')
    ->where($conds)->order($order)->paginate();

    // 合计充值
    $total = table('CYWebSMSDB.dbo.ChargeOnline a', 'rydb.websms')
            ->join('CYBasicDB.dbo.AccountsInfo c', 'c.UserID = a.UserID', 'LEFT')
            ->join('CYBasicDB.dbo.AccountsGameCurrency e', 'e.UserID = a.UserID', 'LEFT')
            ->join('CYBasicDB.dbo.AccountsInfo f', 'f.UserID = c.SpreaderID', 'LEFT')
            ->where($conds)
            ->sum('a.RMB');
    ////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'Channel'   => $Channel,
      'search'    => $search,
      'time1'     => $time1,
      'time2'     => $time2,
      'list'      => $list,
      'state1'    => $state1,
      'state2'    => $state2,
      'ptype'     => $ptype,
      'total'     => $total,
      'PAY_TYPES' => self::PAY_TYPES,
      'STATE_PAY' => $STATE_PAY,
      'STATE_ORD' => $STATE_ORD,
      'CHANNELS'  => $CHANNELS,
      'KindID'    => $KindID,
      'GAME_KIND' => $GAME_KIND,
    ]);
  }
  /**
   *  @brief  : 用户管理-在线支付统计
   *  @return : page
   *  @details: 
   */
  public function trendAction() {
    $conds = ['a.Status' => 1, 'a.GameStatus' => 1];
    $CHANNELS   = $this->GetAdminChannel();
    $GAME_KIND  = GetGameKind();
    ////////////////////////////////////////////////////////////////////////////
    $Channel = $this->request->param('Channel/d', -1);
    if ($Channel > 0 && array_key_exists($Channel, $CHANNELS)) {
      $conds['e.Channel'][] = ['IN', array_keys($CHANNELS)];
      $conds['e.Channel'][] = ['EQ', $Channel];
    } else {
      $conds['e.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    ////////////////////////////////////////
    $KindID = $this->request->param('KindID/d', -1);
    // if ($KindID > 0 && array_key_exists($KindID, $GAME_KIND)) {
      // $conds['e.KindID'] = $KindID;
    // }
    ////////////////////////////////////////
    $type = $this->request->param('type/s', -1);
    $type = array_key_exists($type, self::PAY_TYPES) ? $type : -1;
    if ($type != -1) {
      $conds['a.PayType'] = self::PAY_TYPES[$type];
    }
    $time1 = $this->request->param('time1/s', '', 'trim,YmdHis2Unix');
    $time2 = $this->request->param('time2/s', '', 'trim,YmdHis2Unix');
    $time2 = ($time2 > 0 && $time2 <= time()) ? $time2 : time();
    $time1 = ($time1 > 0 && $time1 <= $time2) ? $time1 : time() - 15 * 24 * 60 * 60; // 默认半个月的数据
    $time1 = date('Y-m-d 00:00:00', $time1);
    $time2 = date('Y-m-d 23:59:59', $time2);
    $conds['a.Addtime'] = ['BETWEEN TIME', [$time1, $time2]];
    ////////////////////////////////////////////////////////////////////////////
    $sql1 = table('CYWebSMSDB.dbo.ChargeOnline a', 'rydb.websms')->field(['MIN(a.ID)'=>'ID'])
            ->join('CYBasicDB.dbo.AccountsInfo e', 'e.UserID = a.UserID', 'LEFT')
            ->where($conds)->group('a.MerBillNo')->buildSql();
    
    $sql1 = table('CYWebSMSDB.dbo.ChargeOnline a', 'rydb.websms')->field('a.*')
            ->join("{$sql1} b", 'b.ID = a.ID', 'RIGHT')->buildSql();
    ////////////////////////////////////////////////////////////////////////////
    $list = [];
    $sql2 = table("{$sql1} a", 'rydb.websms')->field([
      'CONVERT(VARCHAR(10), [a].[Addtime], 120)' => 'y',
      'COUNT(*)' => 'c',
    ])
    ->group('CONVERT(VARCHAR(10), [a].[Addtime], 120)')->buildSql();
    $total = table("{$sql2} b", 'rydb.websms')->count();  // 按日期分组满足条件有几条

    if ($total > 0) {
      $list = table("{$sql1} a", 'rydb.websms')->field([
        'CONVERT(VARCHAR(10), [a].[Addtime], 120)' => 'y',
        'DATEPART(hh, [a].[Addtime])' => 'h',
        'COUNT(*)' => 'c',
        'SUM([a].[RMB])'  => 's',
      ])
      ->group('CONVERT(VARCHAR(10), [a].[Addtime], 120), DATEPART(hh, [a].[Addtime])')
      ->order(['y' => 'DESC', 'h'])->select(); // 按日期小时分组
    }
    $path = Request::instance()->baseUrl();
    $page = new \org\dtcms($list, $total <= 0 ? 1 : $total, 1, $total, false, ['path' => $path]);
    ////////////////////////////////////////////////////////////////////////////
    $tree = [];
    $total_count = 0;
    $total_sum   = 0;
    foreach($list as $a) {
      $y = $a['y'];
      if (!isset($tree[$y])) {
        $tree[$y] = ['total' => 0, 'sum' => 0, 'child' => []];
      }
      $total_count += $a['c'];
      $total_sum += $a['s'];
      $tree[$y]['total'] += $a['c'];
      $tree[$y]['sum'] += $a['s'];
      $tree[$y]['child'][$a['h']] = $a;
    }
    ////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'type'  => $type,
      'page'  => $page,
      'tree'  => $tree,
      'time1' => $time1,
      'time2' => $time2,
      'total_count' => $total_count,
      'total_sum' => $total_sum,
      'PAY_TYPES' => self::PAY_TYPES,
      'CHANNELS'  => $CHANNELS,
      'GAME_KIND' => $GAME_KIND,
      'KindID'    => $KindID,
      'Channel'   => $Channel,
    ]);
  }
  /**
   *  @brief  : 补发佣金
   *  @return : json
   *  @details: 
   */
  public function reissueAction() {

    return result([], 110, '操作失败');
    $MerBillNo = $this->request->param('MerBillNo/s', '');
    $bill = table('CYWebSMSDB.dbo.ChargeOnline a', 'rydb.websms')->field([
      'a.ID', 'a.GameID', 'a.UserID', 'a.MerBillNo', 'a.TradeNo', 'a.RMB', 'a.Addtime',
      'a.Status', 'a.GameStatus', 'b.SpreaderID', 'b.NickName',
    ])
    ->join('CYBasicDB.dbo.AccountsInfo b', 'b.UserID = a.UserID', 'LEFT')
    ->where('MerBillNo', $MerBillNo)->find();

    if (empty($bill)) {
      return result([], 1, '该订单不存在');
    }
    if ($bill['Status'] != 1) {
      return result([], 2, '该订单还未完成支付');
    }
    if ($bill['SpreaderID'] == 0) {
      return result([], 3, '该订单用户未绑定代理');
    }

    $AgentID = $bill['SpreaderID'];
    // 循环写入代理返点信息
    do {
      
      $agent = table('CYWebSMSDB.dbo.AgentReg c', 'rydb.websms')->field([
        'c.UserID'=>'ID', 'c.ParentId'=>'ParentID', 'c.Compellation', 'c.AgentLevel',
        'c.DirectLevel', 'c.MyAgent', 'c.Status', 'c.IsDirect'=>'IsDirect',
        'c.level0', 'c.level1', 'c.level2', 'c.level3', 'c.level4', 'c.level5', 'c.level6',
      ])->where('c.UserID', $AgentID)->find();

      if ($agent['ID'] == 0) {
        return result([], 4, "代理:{$bill['SpreaderID']}不存在");
      }
      if ($agent['Status'] == 0) {
        return result([], 5, "代理:{$agent['ID']}未审核通过");
      }

      if ($agent['IsDirect']) {
        $agent['Level'] = $agent['DirectLevel'];
      } else {
        if ($agent['AgentLevel'] >= 1 && $agent['AgentLevel'] <= 7) {
          $agent['Level'] = $agent['level'.($agent['AgentLevel'] - 1)];
        } else {
          $agent['Level'] = 0;
        }
      }

      if ($agent['Level'] == 0) {
        return result([], 6, "代理:{$agent['ID']}未设置返点比率");
      }

      $myRebate = $bill['RMB'] * $agent['Level'] / 100; // 计算返点金额
      if ($myRebate <= 0) {
        break;
      }
      
      // 是否存在已返记录
      $eax = table('AgentChargeRebate', 'rydb.websms')->where([
        'AgentID'=>$agent['ID'], 'MerBillNo'=>$bill['MerBillNo'], 'TradeNo'=>$bill['TradeNo'],
      ])->count();

      if ($eax == 0) { // 写入记录
        table('AgentChargeRebate', 'rydb.websms')->insert([
          'AgentID'     => $agent['ID'],
          'MerBillNo'   => $bill['MerBillNo'],
          'TradeNo'     => $bill['TradeNo'],
          'RMB'         => $bill['RMB'],
          'myRebate'    => $myRebate,
          'myAgentLevel'=> $agent['AgentLevel'],
          'myLevel'     => $agent['Level'],
          'Addtime'     => new Expression('GETDATE()'),
        ]);
      }
      if ($agent['ParentID'] > 0) { // 代理还有上级代理
        $AgentID = $agent['ParentID'];
      } else {
        break;
      }
    } while (true);
    return result([], 0);
  }
  /**
   *  @brief  : 代理返点记录
   *  @return : page
   *  @details: 
   */
  public function rebateAction() {
    $conds = [];
    $order = [];
    $CHANNELS = $this->GetAdminChannel();
    $AGENT_TYPE = GetAgentType();
    ////////////////////////////////////////////////////////////////////////////
    $channel = $this->request->param('channel/d', -1);
    if ($channel > 0 && array_key_exists($channel, $CHANNELS)) {
      $conds['e.Channel'][] = ['IN', array_keys($CHANNELS)];
      $conds['e.Channel'][] = ['EQ', $channel];
    } else {
      $conds['e.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    //////////////////////////////////////////////
    $AgentType = $this->request->param('AgentType/d', -1);
    $AgentType = array_key_exists($AgentType, $AGENT_TYPE) ? $AgentType : -1;
    if ($AgentType > 0) {
      $conds['d.AgentType'] = $AgentType;
    }
    //////////////////////////////////////////////
    $search = $this->request->param('search/s', '', 'trim');
    if (!empty($search)) {
      if (is_numeric($search) && $search > 0 && $search < PHP_INT_MAX) {
        $conds['e.UserID|e.GameID'] = $search;
      } else {
        $conds['e.NickName|g.MerBillNo'] = ['LIKE', "%{$search}%"];
      }
    }
    //////////////////////////////////////////////
    $yqcode = $this->request->param('yqcode/d', 0);
    if ($yqcode > 0) {
      $conds['d.GameID'] = $yqcode;
    }
    //////////////////////////////////////////////
    $time1 = $this->request->param('time1/s', '', 'trim');
    $time2 = $this->request->param('time2/s', '', 'trim');
    $aa = YmdHis2Unix($time1);
    $bb = YmdHis2Unix($time2);
    $ta = YmdHis($aa);
    $tb = YmdHis($bb);
    if ($aa > 0) {
      if ($bb > 0) {
        if ($aa <= $bb) {
          $conds['g.Addtime'] = ['BETWEEN TIME', [$ta, $tb]];
        } else {
          $conds['g.Addtime'] = ['>= TIME', $ta];
        }
      } else {
        $conds['g.Addtime'] = ['>= TIME', $ta];
      }
    } else {
      if ($bb > 0) {
        $conds['g.Addtime'] = ['<= TIME', $tb];
      }
    }

    /////////////////////////////////////////////
    $orderMap = [
      'A' => 'Channel',
      'B' => 'MerBillNo',
      'C' => 'GameID',
      'D' => 'AgentGameID',
      'E' => 'myAgentLevel',
      'F' => 'RMB',
      'G' => 'myRebate',
      'H' => 'myLevel',
      'I' => 'Addtime',
    ];
    $this->orderField = array_key_exists($this->orderField, $orderMap) ? $this->orderField : 'B';
    $order[$orderMap[$this->orderField]] = $this->orderDirection;
    ////////////////////////////////////////////////////////////////////////////
    $sql2 = table('CYWebSMSDB.dbo.AgentChargeRebate a', 'rydb.websms')->field([
      'a.MerBillNo', 'b.UserID', 'a.AgentID',
    ])
    ->join('CYWebSMSDB.dbo.ChargeOnline b', 'b.MerBillNo = a.MerBillNo', 'LEFT')
    ->where([
      'a.IsDisable' => 0,
      'b.Status' => 1,
      'b.GameStatus' => 1,
    ])
    ->group('a.MerBillNo, b.UserID, a.AgentID')
    ->buildSql();
    
    $list = table("{$sql2} c", 'rydb.websms')->field([
      'c.AgentID', 'c.MerBillNo', 'c.UserID', 'd.AgentType',
      'd.AgentLevel', 'd.Compellation' => 'AgentCompellation', 'd.GameID' => 'AgentGameID',
      'e.AccountsType', 'e.Channel', 'e.GameID', 'e.NickName',
      'e.AccountImgUrl',
      'g.Addtime', 'g.ID', 'g.RMB', 'g.myAgentLevel', 'g.myLevel', 'g.myRebate',
    ])
    ->join('CYWebSMSDB.dbo.AgentReg d', 'd.UserID = c.AgentID', 'LEFT')
    ->join('CYBasicDB.dbo.AccountsInfo e', 'e.UserID = c.UserID', 'LEFT')
    ->join('CYWebSMSDB.dbo.AgentChargeRebate g', 'g.MerBillNo = c.MerBillNo AND g.AgentID = c.AgentID', 'LEFT')
    ->where($conds)->order($order)->paginate();
    ////////////////////////////////////////////////
    $sql3 = table("{$sql2} c", 'rydb.websms')->field(['c.MerBillNo', 'g.RMB'])
      ->join('CYWebSMSDB.dbo.AgentReg d', 'd.UserID = c.AgentID', 'LEFT')
      ->join('CYBasicDB.dbo.AccountsInfo e', 'e.UserID = c.UserID', 'LEFT')
      ->join('CYWebSMSDB.dbo.AgentChargeRebate g', 'g.MerBillNo = c.MerBillNo AND g.AgentID = c.AgentID', 'LEFT')
      ->where($conds)
      ->group('c.MerBillNo, g.RMB')
      ->buildSql();
    $totalRMB = table("{$sql3} h", 'rydb.websms')->sum('h.RMB');
    ////////////////////////////////////////////////
    $totalRebate = table("{$sql2} c", 'rydb.websms')
      ->join('CYWebSMSDB.dbo.AgentReg d', 'd.UserID = c.AgentID', 'LEFT')
      ->join('CYBasicDB.dbo.AccountsInfo e', 'e.UserID = c.UserID', 'LEFT')
      ->join('CYWebSMSDB.dbo.AgentChargeRebate g', 'g.MerBillNo = c.MerBillNo AND g.AgentID = c.AgentID', 'LEFT')
      ->where($conds)
      ->sum('g.myRebate');
    ////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'AGENT_TYPE'=> $AGENT_TYPE,
      'CHANNELS'  => $CHANNELS,
      'channel'   => $channel,
      'AgentType' => $AgentType,
      'time1'   => $time1,
      'time2'   => $time2,
      'list'    => $list,
      'search'  => $search,
      'yqcode'  => $yqcode == 0 ? '' : $yqcode,
      'totalRMB'    => $totalRMB??0,
      'totalRebate' => $totalRebate??0,
    ]);
  }
  /**
   *  @brief  : 充值排行榜
   *  @return : page
   *  @details: 
   */
  public function rankAction() {
    $list = [];
    $conds = ['a.Status' => 1, 'a.GameStatus' => 1];
    $cond2 = [];
    $order = [];
    $CHANNELS   = $this->GetAdminChannel();
    $GAME_KIND  = GetGameKind();
    ////////////////////////////////////////////////////////////////////////////
    $Channel = $this->request->param('Channel/d', -1);
    if ($Channel > 0 && array_key_exists($Channel, $CHANNELS)) {
      $cond2['c.Channel'][] = ['IN', array_keys($CHANNELS)];
      $cond2['c.Channel'][] = ['EQ', $Channel];
    } else {
      $cond2['c.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    //////////////////////////////
    $KindID = $this->request->param('KindID/d', -1);
    if ($KindID > 0 && array_key_exists($KindID, $GAME_KIND)) {
      $conds['a.KindID'] = $KindID;
    }
    //////////////////////////////
    $time1 = $this->request->param('time1/d', 0, 'trim,YmdHis2Unix');
    $time2 = $this->request->param('time2/d', 0, 'trim,YmdHis2Unix');
    $timea = YmdHis($time1);
    $timeb = YmdHis($time2);
    if ($time1 > 0) {
      if ($time2 > 0) {
        if ($time1 <= $time2) {
          $conds['Addtime'] = ['BETWEEN TIME', [$timea, $timeb]];
        } else {
          $conds['Addtime'] = ['>= TIME', $timea];
        }
      } else {
        $conds['Addtime'] = ['>= TIME', $timea];
      }
    } else {
      if ($time2 > 0) {
        $conds['Addtime'] = ['<= TIME', $timeb];
      }
    }
    //////////////////////////////////////////////
    $search = $this->request->param('search/s', '', 'trim');
    if (!empty($search)) {
      if (is_numeric($search) && $search > 0 && $search < PHP_INT_MAX) {
        $cond2['c.UserID|c.GameID'] = $search;
      } else {
        $cond2['c.NickName'] = ['LIKE', "%{$search}%"];
      }
    }
    //////////////////////////////////////////////
    $yqcode = $this->request->param('yqcode/d', 0);
    if ($yqcode > 0) {
      $cond2['f.GameID'] = $yqcode;
    }
    ////////////////////////////////////////////////////////////////////////////
    $orderMap = [
      'B' => 'Channel',
      'C' => 'KindID',
      'D' => 'GameID',
      'E' => 'AGameID',
      'F' => 'RoomCard',
      'G' => 'PlayCount',
      'H' => 'PayCount',
      'I' => 'PayAmount',
      'J' => 'Socre7',
      'K' => 'SocreAmount',
    ];
    $this->orderField = array_key_exists($this->orderField, $orderMap) ? $this->orderField : 'I';
    $order[$orderMap[$this->orderField]] = $this->orderDirection;
    ////////////////////////////////////////////////////////////////////////////
    $time = YmdHis2Unix(date('Y-m-d 00:00:00'));
    $sql2 = table('ChargeOnline a', 'rydb.websms')->field([
      'a.UserID', 'a.KindID', 'SUM (a.RMB)' => 'PayAmount', 'COUNT(ID)' => 'PayCount',
    ])->where($conds)->group('a.UserID, a.KindID')->buildSql();
    
    $sql2 = sql_pure($sql2);

    $list = table("{$sql2} b", 'rydb.websms')->field([
      'b.UserID', 'b.KindID', 'b.PayAmount', 'b.PayCount',
      'c.GameID', 'c.Accounts', 'c.NickName', 'c.PassPortID', 'c.Compellation',
      'c.RegisterDate', 'c.RegisterIP', 'c.GameLogonCount', 'c.LastLogonDate',
      'c.LastLogonIP', 'e.RoomCard', 'c.SpreaderID' => 'AUserID', 'j.GameName',
      'f.GameID' => 'AGameID', 'f.Compellation' => 'ACompellation', 'f.AccountImgUrl' => 'AAvatar',
      'c.Sex', 'c.Channel', 'c.AccountsType', 'c.AccountImgUrl' => 'Avatar', 'c.RegisterMobile',
      '(SELECT COUNT(g.RecordID) FROM CYRecordDB.dbo.RecordCreateTable g WHERE g.UserID = b.UserID AND g.KindID = b.KindID AND g.RecordType IN (3, 4))' => 'PlayCount', // 游戏次数
              '(SELECT ISNULL(SUM (g.lSocre), 0) FROM CYRecordDB.dbo.PrivateGameRecordUserRecordID g WHERE g.UserID = b.UserID AND g.dwKindID = b.KindID)' => 'SocreAmount',
      sprintf("(SELECT ISNULL(SUM (g.lSocre), 0) FROM CYRecordDB.dbo.PrivateGameRecordUserRecordID g WHERE g.UserID = b.UserID AND g.dwKindID = b.KindID AND g.InsertTime BETWEEN '%s' AND '%s' )",
        YmdHis($time - 6 * 24 * 60 * 60),
        YmdHis($time + 1 * 24 * 60 * 60)
      ) => 'Socre7',
    ])
    ->join('CYBasicDB.dbo.AccountsInfo c',     'c.UserID = b.UserID', 'LEFT')
    ->join('CYBasicDB.dbo.AccountsGameCurrency e',  'e.UserID = b.UserID', 'LEFT')
    ->join('CYBasicDB.dbo.AccountsInfo f',     'f.UserID = c.SpreaderID', 'LEFT')
    ->join('CYBasicDB.dbo.GameKindItem j',     'j.KindID = b.KindID', 'LEFT')
    ->where($cond2)->order($order)->paginate();
    ////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'CHANNELS'  => $CHANNELS,
      'GAME_KIND' => $GAME_KIND,
      'Channel' => $Channel,
      'KindID'  => $KindID,
      'search'  => $search,
      'yqcode'  => $yqcode == 0 ? '' : $yqcode,
      'list'    => $list,
      'time'    => $time,
      'time1'   => $time1,
      'time2'   => $time2,
    ]);
  }
  /**
   *  @brief  : 渠道充值统计
   *  @return : page
   *  @details: 
   */
  public function chartsAction() {
    $DAY_SPAN = 7;
    $CHANNELS = $this->GetAdminChannel();
  
    $time2 = $this->request->param('time/s', 0, 'trim,YmdHis2Unix');
    $time2 = ($time2 > 0 && $time2 <= time()) ? $time2 : time();
    $time1 = $time2 - ($DAY_SPAN - 1) * 24 * 60 * 60; // 默认两周的数据
    $time1 = date('Y-m-d 00:00:00', $time1);
    $time3 = date('Y-m-d 00:00:00', $time2 + 1 * 24 * 60 * 60);
    $conds = ['a.Status' => 1, 'a.GameStatus' => 1, 'a.Addtime' => ['BETWEEN TIME', [$time1, $time3]]];
    $conds['b.Channel'] = ['IN', array_keys($CHANNELS)];
    $sql2 = table('CYWebSMSDB.dbo.ChargeOnline a', 'rydb.websms')->field([
      'a.MerBillNo',
      'b.Channel',
      'CONVERT (VARCHAR(10), a.Addtime, 120)' => 'Ymd',
      'SUM (DISTINCT a.RMB)' => 'RMB',
    ])
    ->join('CYBasicDB.dbo.AccountsInfo b', 'b.UserID = a.UserID', 'LEFT')
    ->where($conds)
    ->group('a.MerBillNo, a.Addtime, b.Channel')
    ->buildSql();
    $sql2 = sql_pure($sql2);

    $list = table("{$sql2} t", 'rydb.websms')->field([
      't.Ymd', 't.Channel', 'SUM (t.RMB)' => 'RMB'
    ])->group('t.Ymd, t.Channel')->select();

    return $this->fetch('', [
      'time'      => date('Y-m-d', $time2),
      'DAY_SPAN'  => $DAY_SPAN,
      'list'      => $list,
      'CHANNELS'  => $CHANNELS,
    ]);
  }
}
